package com.weixin1.dao;

import com.weixin1.bean.Photo;
import com.weixin1.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class PhotoDao {
    private QueryRunner qr = new QueryRunner(C3p0Utils.comboPooledDataSource);
    public List<Photo> list() throws SQLException {
        return qr.query("select p_id pId,p_name pName,p_path pPath,e_name eName from photo",new BeanListHandler<>(Photo.class));
    }
     public Photo getById(Long id) throws SQLException {
        return qr.query("select p_name pName,p_path pPath,e_name eName from photo where p_id=?",new BeanHandler<>(Photo.class),id);
     }

     public int save(Photo photo) throws SQLException {
        return  qr.update("insert into photo(p_name,p_path,e_name) values(?,?,?)",photo.getPName(),photo.getPPath(),photo.getEName());
     }

     public int removeById(Long id) throws SQLException {
        return qr.update("delete from photo where p_id=?",id);
     }

     public int update(Photo photo) throws SQLException {
        return  qr.update("update photo set p_name=?,p_path=?,e_name=? where p_id=?",photo.getPName(),photo.getPPath(),photo.getEName(),photo.getPId());
     }

     //分页查询
    public List<Photo> selectByPage(int page,int limit) throws SQLException {
        return qr.query("select p_id pId,p_name pName,p_path pPath,e_name eName from photo " +
                "limit ?,?",new BeanListHandler<>(Photo.class),(page-1)*limit,limit);
    }
    //获取总数据条数
    public int gettotal() throws SQLException {
        return qr.query("select count(*) from photo",new ScalarHandler<Long>()).intValue();
    }
}
